﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;
using log4net;

namespace DAO
{
    public sealed class Connection
    {

        public static readonly string CONN_STRING1 = ConfigurationSettings.AppSettings["ConnString1"];
        public static readonly string CONN_STRING2 = ConfigurationSettings.AppSettings["ConnString2"];
        public static readonly int DEFAULT_LANGUAGE = 1;
        protected static readonly ILog log = LogManager.GetLogger("Ayurveda"); //for logging
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        //ova ni treba za insert i update
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, bool insert, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (insert)
                {
                    cmdText += ";Select @@Identity";
                }
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = 0;
                if (insert)
                {
                    Int32.TryParse(cmd.ExecuteScalar().ToString(), out val);
                }
                else
                {
                    val = cmd.ExecuteNonQuery();
                }
                cmd.Parameters.Clear();
                
                return val;
            }
        }
        //ova ni treba za insert i update
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static DataSet ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            DataSet dsReturn = new DataSet();
            try
            {
                
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                dataAdapter.Fill(dsReturn);
                cmd.Parameters.Clear();

                //return dsReturn;
            }
            catch (SqlException odbcEx) 
            {
                dsReturn = new DataSet();
                dsReturn.Tables.Add(new DataTable("Error"));
                DataColumn c = new DataColumn("message");
                dsReturn.Tables["Error"].Columns.Add(c);
                DataRow r = dsReturn.Tables["Error"].NewRow();
                r["message"] = odbcEx.Message.ToString();
                dsReturn.Tables["Error"].Rows.Add(r);
                log.Info("Log started");
                log.Error("Error exception: ", odbcEx);
                log.Info("Log ended\r\n\r\n-------------------------------------\r\n");
                //there is a problem with the connection
            }
            catch(Exception ex)
            {
                log.Info("Log started");
                log.Error("Error exception: ", ex);
                log.Info("Log ended\r\n\r\n-------------------------------------\r\n");
                //throw;
            }
            finally
            {
                conn.Close();
            }
            return dsReturn;
            
   // Handle more specific SqlException exception here.
        }
        
        public static void ExecuteReaderSP(string sp, CommandType cmdType, 
            ref SqlDataReader dr, SqlConnection conn,
            params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            if (cmdParms!=null)
                foreach (SqlParameter s in cmdParms)
                    cmd.Parameters.Add(s);
            conn = new SqlConnection(CONN_STRING1);
            cmd.Connection = conn;
            conn.Open();
            try
            {
                dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();

                //return dsReturn;
            }
            catch (SqlException odbcEx)
            {
                //log.Info("Log started");
                //log.Error("Error exception: ", odbcEx);
                //log.Info("Log ended\r\n\r\n-------------------------------------\r\n");
                //there is a problem with the connection
            }
            catch (Exception ex)
            {
                //log.Info("Log started");
                //log.Error("Error exception: ", odbcEx);
                //log.Info("Log ended\r\n\r\n-------------------------------------\r\n");
                //throw;
            }

            // Handle more specific SqlException exception here.
        }

        public static int ExecuteNonQuerySP(string sp, SqlConnection conn, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            conn = new SqlConnection(CONN_STRING1);
            foreach (SqlParameter s in cmdParms)
                cmd.Parameters.Add(s);
            cmd.Connection = conn;
            conn.Open();
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            conn.Close();
            return val;
        }
        private static object HandleException(SqlException odbcEx)
        {
            string returnString = "";
            switch (odbcEx.Number)
            {
                case 4060: // Invalid Database 
                    returnString = "Invalid database specified for connection!!! Please contact system administrator!";
                    break;
                case 18456: // Login Failed 
                    returnString = "Login failed for database connection!!! Please contact your system administrator!";
                    break;
                case 547: // ForeignKey Violation
                    //returnString = "";
                    break;
                case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
                    break;
                case 2601: // Unique Index/Constriant Violation
                    break;
                default:
                    break;
            }
            return "";
        }
        public static bool HasRecords(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);
            DataSet dsReturn = new DataSet();
            try
            {   
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleResult);

                return dr.HasRows;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        //za count na podatoci ili za insert i update
        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        public static int Log(long userid, string action)
        {
            SqlCommand cmd = new SqlCommand();
            string cmdText = "INSERT INTO [ayurvead_db].[dbo].[Log] ([date],[time],[userid],[action]) "
                    +" VALUES(@date, "
                        + "@time ,"
                        + "@userid ,@action)";
            SqlParameter[] pars = new SqlParameter[4];
            pars[0] = new SqlParameter("date", DateTime.Today);
            pars[1] = new SqlParameter("time", new TimeSpan(DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second).ToString());
            pars[2] = new SqlParameter("userid", userid);
            pars[3] = new SqlParameter("action", action);
            using (SqlConnection conn = new SqlConnection(CONN_STRING1))
            {
                conn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                foreach (SqlParameter parm in pars)
                    cmd.Parameters.Add(parm);
                try
                {
                    cmd.ExecuteScalar();
                }
                catch { }
                if (conn.State == ConnectionState.Open)
                    conn.Close();

                return 1;
            }
        }
    }
}